﻿Public Class ClsTDetailReportDbAccess
    Inherits ClsBaseDbAccess

    Public Sub New(ByVal s As String)
        MyBase.New(s, TABLE_NAME_T_DETAIL_REPORT)
    End Sub

    Public Sub New(ByVal s As String, ByVal n As String)
        MyBase.New(s, n)
    End Sub


    ''' <summary>
    ''' 删除指定期间的科目余额
    ''' </summary>
    ''' <param name="wp"></param>
    ''' <returns></returns>
    Public Function DeleteDetailReportByWorkPeriod(ByVal wp As String) As Integer

        Dim tmpRetInt As Integer

        Try

            Dim tmpSql As String
            Dim tmpParas(0) As OleDb.OleDbParameter
            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "Delete * from [" & Me.AppTableName & "] "
            tmpSql &= "where  [WorkPeriod]=@WorkPeriod "

            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(0).Value = wp

            tmpRetInt = tmpDbAccess.UpdateRecord(tmpSql, tmpParas)

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTDetailReportDbAccess - DeleteDetailReportByWorkPeriod"

        End Try

        Return tmpRetInt

    End Function

    ''' <summary>
    ''' 插入一条新的三栏账数据
    ''' </summary>
    ''' <param name="tmpCls"></param>
    ''' <returns></returns>
    Public Function InsertDetailReport(ByVal tmpCls As ClsTDetailReport) As Integer

        Dim tmpRetInt As Integer

        Try

            Dim tmpSql As String
            Dim tmpParas(13) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "Insert into [" & Me.AppTableName & "] ("
            tmpSql &= "[WorkPeriod],[AcctCode],[AcctLevel],[AcctFullName],"
            tmpSql &= "[VoucherId],[VoucherGroupId],[VoucherGroupShorName],[VoucherNumber],[Exp],"
            tmpSql &= "[Credit],[Debit],[BeginBalance],[EndBalance],[BalanceDirection]"
            tmpSql &= ") Values ("
            tmpSql &= "@WorkPeriod,@AcctCode,@AcctLevel,@AcctFullName,"
            tmpSql &= "@VoucherId,@VoucherGroupId,@VoucherGroupShorName ,@VoucherNumber,@Exp,"
            tmpSql &= "@Credit,@Debit,@BeginBalance,@EndBalance,@BalanceDirection"
            tmpSql &= ")"

            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(1) = New OleDb.OleDbParameter("@AcctCode", OleDb.OleDbType.VarWChar)
            tmpParas(2) = New OleDb.OleDbParameter("@AcctLevel", OleDb.OleDbType.Integer)
            tmpParas(3) = New OleDb.OleDbParameter("@AcctFullName", OleDb.OleDbType.VarWChar)
            tmpParas(4) = New OleDb.OleDbParameter("@VoucherId", OleDb.OleDbType.Integer)
            tmpParas(5) = New OleDb.OleDbParameter("@VoucherGroupId", OleDb.OleDbType.Integer)
            tmpParas(6) = New OleDb.OleDbParameter("@VoucherGroupShortName", OleDb.OleDbType.VarWChar)
            tmpParas(7) = New OleDb.OleDbParameter("@VoucherNumber", OleDb.OleDbType.Integer)
            tmpParas(8) = New OleDb.OleDbParameter("@Exp", OleDb.OleDbType.VarWChar)
            tmpParas(9) = New OleDb.OleDbParameter("@Credit", OleDb.OleDbType.Decimal)
            tmpParas(10) = New OleDb.OleDbParameter("@Debit", OleDb.OleDbType.Decimal)
            tmpParas(11) = New OleDb.OleDbParameter("@BeginBalance", OleDb.OleDbType.Decimal)
            tmpParas(12) = New OleDb.OleDbParameter("@EndBalance", OleDb.OleDbType.Decimal)
            tmpParas(13) = New OleDb.OleDbParameter("@BalanceDirection", OleDb.OleDbType.VarWChar)

            With tmpCls

                tmpParas(0).Value = .WorkPeriod
                tmpParas(1).Value = .AcctCode
                tmpParas(2).Value = .AcctLevel
                tmpParas(3).Value = .AcctFullName
                tmpParas(4).Value = .VoucherId
                tmpParas(5).Value = .VoucherGroupId
                tmpParas(6).Value = .VoucherGroupShorName
                tmpParas(7).Value = .VoucherNumber
                tmpParas(8).Value = .Exp
                tmpParas(9).Value = .Credit
                tmpParas(10).Value = .Debit
                tmpParas(11).Value = .BeginBalance
                tmpParas(12).Value = .EndBalance
                tmpParas(13).Value = .BalanceDirection

            End With


            tmpRetInt = tmpDbAccess.InsertData(tmpSql, tmpParas, True)

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTAccountSumDbAccess - InsertAccountSum"

        End Try

        Return tmpRetInt

    End Function


    Public Overrides Function ConvertRecordToClass(reader As OleDb.OleDbDataReader) As Object

        If reader Is Nothing Then
            Return Nothing
        End If

        Dim tmpList As List(Of ClsTDetailReport) = New List(Of ClsTDetailReport)

        Try

            Dim tmpCls As ClsTDetailReport

            While reader.Read()

                tmpCls = New ClsTDetailReport()

                With tmpCls

                    .DetailReportId = reader.GetInt32(0)
                    .WorkPeriod = reader.GetString(1)
                    .AcctCode = reader.GetString(2)
                    .AcctLevel = reader.GetInt16(3)
                    .AcctFullName = reader.GetString(4)
                    .VoucherId = reader.GetInt32(5)
                    .VoucherGroupId = reader.GetInt32(6)
                    .VoucherGroupShorName = reader.GetString(7)
                    .VoucherNumber = reader.GetInt32(8)
                    .Exp = reader.GetString(9)
                    .Credit = reader.GetDecimal(10)
                    .Debit = reader.GetDecimal(11)
                    .BeginBalance = reader.GetDecimal(12)
                    .EndBalance = reader.GetDecimal(13)
                    .BalanceDirection = reader.GetString(14)

                End With

                tmpList.Add(tmpCls)

            End While

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTDetailReportDbAccess - ConvertRecordToClass"

        End Try

        Return tmpList

    End Function
End Class
